package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQLProduktBatchDAO implements ProduktBatchDAO {

	private Connector conn = null;
	private ResultSet rs = null;

	public MySQLProduktBatchDAO() throws DALException {
		conn = new Connector();
	}
	
	public ProduktBatchDTO getProduktBatch(int pbId) throws DALException {
		ProduktBatchDTO pbDTO = null;
		String query = "SELECT * FROM produktbatch WHERE pbId= " + pbId + ";";
		rs = conn.doQuery(query);
		try {
			if (!rs.first()) {
				throw new DALException("ProduktBatch " + pbId + "findes ikke");

			}
			pbDTO = new ProduktBatchDTO(rs.getInt(1), rs.getInt(2),
					rs.getInt(3),rs.getDate(4), rs.getDate(5));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pbDTO;

	}

	public List<ProduktBatchDTO> getProduktBatchList() throws DALException {
		List<ProduktBatchDTO> pbDTO = new ArrayList<ProduktBatchDTO>();
		String query = "SELECT * FROM produktbatch";
		rs = conn.doQuery(query);

		try {
			while (rs.next()) {
			
			pbDTO.add(new ProduktBatchDTO(rs.getInt(1), rs.getInt(2), rs
					.getInt(3),rs.getDate(4),rs.getDate(5)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return pbDTO;
	}

	public void createProduktBatch(ProduktBatchDTO produktbatch)
			throws DALException {
		conn.doUpdate("INSERT INTO produktbatch(pbId, receptId, status1,startDato,slutDato) VALUES"
				+ "("
				+ produktbatch.getPbId()
				+ ","
				+ produktbatch.getReceptId()
				+ ","
				+ produktbatch.getStatus()
				+","
				+ produktbatch.getStartDato()
				+ ","
				+ produktbatch.getSlutDato()
				+ ");");

	}
	
	public void createProduktBatchTest(ProduktBatchDTO produktbatch)
			throws DALException {
		conn.doUpdate("INSERT INTO produktbatch(pbId, receptId, status1,startDato,slutDato) VALUES"
				+ "(NULL,"
				+ produktbatch.getReceptId()
				+ ","
				+ produktbatch.getStatus()
				+",NULL, NULL);");

	}

	public void updateProduktBatch(ProduktBatchDTO produktbatch)
			throws DALException {
		String start;
		String end;
		if (produktbatch.getStartDato() == null)
			start = "NULL";
		else
			start = "\"" + produktbatch.getStartDato().toString() + "\"";
		
		if (produktbatch.getSlutDato() == null)
			end = "NULL";
		else
			end = "\"" + produktbatch.getSlutDato().toString() + "\"";
		
		conn.doUpdate("UPDATE produktbatch SET status1 = "
				+ produktbatch.getStatus() + ", startDato ="
				+ start + ", slutDato = "
				+ end + " WHERE pbId= "
				+ produktbatch.getPbId() + " AND receptId = "
				+ produktbatch.getReceptId());
	}

	public List<ProduktBatchDTO> getProduktBatchList(int first, int count)
			throws DALException {
		List<ProduktBatchDTO> pbDTO = new ArrayList<ProduktBatchDTO>();
		String query = "SELECT * FROM produktbatch LIMIT " + first + "," + count;
		rs = conn.doQuery(query);

		try {
			while (rs.next()) {
				pbDTO.add(new ProduktBatchDTO(rs.getInt(1), rs.getInt(2),rs.getInt(3), rs.getDate(4), rs.getDate(5)));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pbDTO;
	
	}
	
	
	public List<Integer> getProduktBatchIdList() throws DALException {
		List<Integer> receptIdList = new ArrayList<Integer>();
		String query = "SELECT DISTINCT receptId FROM produktbatch";
		rs = conn.doQuery(query);
		try {
			while (rs.next()) {
			receptIdList.add(rs.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return receptIdList;
	}
	
	

	public int getCount() throws DALException {
		
		rs = conn.doQuery("SELECT COUNT(pbId) FROM produktbatch");
		try {
			if (rs.next())
				return rs.getInt(1);
			else
				return 0;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
	}

}
}
